package com.jsc.common.utils;

import com.alibaba.fastjson.JSONArray;
import com.jsc.pojo.Company;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;

/**
 * 查询大数据库
 */
@Slf4j
public class CheckQYXX {

    private static Connection getConn() {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://47.94.171.13:3306/db_hbjsc?autoReconnect=true&relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull";
        String username = "root";
        String password = "jsc1903";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static Company getQyrz(String qyname) {
        String sql = "select  credit_no, org_no, name, econ_kind, term_end,oper_name,address.address,email.email,tel.telephones" +
                " from t_enterprise as en" +
                " left join" +
                " (select DISTINCT aa.eid,address,aa.row_update_time,aa.date from (select eid,id, address, row_update_time,source,date from t_address " +
                " ) aa" +
                " inner join (" +
                "select a.eid, max(a.row_update_time) as 'row_update_time',max(a.date) as 'date' from t_address  a" +
                " inner join t_enterprise b on a.eid=b.eid" +
                " where b.name  ='" + qyname.trim() + "'" +
                " group by eid ) bb on  aa.eid=bb.eid and aa.row_update_time=bb.row_update_time and aa.date=bb.date" +
                ") as address on en.eid=address.eid" +
                " left join" +
                " (select DISTINCT aa.eid, email, aa.row_update_time,aa.date from (select eid,id, value as email, row_update_time,source,date from t_emails " +
                " )aa" +
                " inner join (" +
                "select a.eid, max(a.row_update_time) as 'row_update_time',max(a.date) as 'date' from t_emails a" +
                " inner join t_enterprise b on a.eid=b.eid" +
                " where b.name  ='" + qyname.trim() + "'" +
                " group by eid ) bb on  aa.eid=bb.eid and aa.row_update_time=bb.row_update_time and aa.date=bb.date" +
                ")as email on en.eid=email.eid " +
                " left join" +
                "(select DISTINCT aa.eid,telephones, aa.row_update_time,aa.date from(" +
                "select id,eid, value as telephones, row_update_time,source,date from  t_telephones" +
                ")aa " +
                " inner join (" +
                "select a.eid, max(a.row_update_time) as row_update_time,max(a.date) as 'date' from t_telephones a " +
                " inner join t_enterprise b on a.eid=b.eid" +
                " where b.name  ='" + qyname.trim() + "'" +
                " group by eid ) bb on  aa.eid=bb.eid and aa.row_update_time=bb.row_update_time and aa.date=bb.date) as tel on en.eid=tel.eid" +
                " where en.name='" + qyname.trim() + "'";

        Connection conn = getConn();
        PreparedStatement pstmt;
        Company qyrz = new Company();
        log.info("数据库查询语句---》" + sql);
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                qyrz.setShxydm(rs.getString(1));
                qyrz.setZzjgdm(rs.getString(2));
                qyrz.setQyname(rs.getString(3));
                qyrz.setQyxz(rs.getString(4));
                qyrz.setYyqx(rs.getString(5));
                qyrz.setFrdbname(rs.getString(6));
                qyrz.setQyzcd(rs.getString(7));
                qyrz.setQyemail(rs.getString(8));
                qyrz.setQygdtell(rs.getString(9));

            }
            rs.close();
            pstmt.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        String json = JSONArray.toJSONString(qyrz);
        log.info("企业验证信息---》" + json);
        return qyrz;
    }

    public static void main(String[] args) {
        Company a = getQyrz("福建省万陆建设发展有限公司");
        String json = JSONArray.toJSONString(a);
        System.out.println(json);
    }

}
